{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Find appropriate coordinates for every timezone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get timezones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>subregion</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timezone</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Africa/Abidjan</th>\n",
       "      <td>Africa</td>\n",
       "      <td>None</td>\n",
       "      <td>Abidjan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Africa/Accra</th>\n",
       "      <td>Africa</td>\n",
       "      <td>None</td>\n",
       "      <td>Accra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Africa/Addis_Ababa</th>\n",
       "      <td>Africa</td>\n",
       "      <td>None</td>\n",
       "      <td>Addis Ababa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Africa/Algiers</th>\n",
       "      <td>Africa</td>\n",
       "      <td>None</td>\n",
       "      <td>Algiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Africa/Asmara</th>\n",
       "      <td>Africa</td>\n",
       "      <td>None</td>\n",
       "      <td>Asmara</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Tahiti</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Tahiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Tarawa</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Tarawa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Tongatapu</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Tongatapu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Wake</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Wake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Wallis</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Wallis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>417 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     region subregion        place\n",
       "timezone                                          \n",
       "Africa/Abidjan       Africa      None      Abidjan\n",
       "Africa/Accra         Africa      None        Accra\n",
       "Africa/Addis_Ababa   Africa      None  Addis Ababa\n",
       "Africa/Algiers       Africa      None      Algiers\n",
       "Africa/Asmara        Africa      None       Asmara\n",
       "...                     ...       ...          ...\n",
       "Pacific/Tahiti      Pacific      None       Tahiti\n",
       "Pacific/Tarawa      Pacific      None       Tarawa\n",
       "Pacific/Tongatapu   Pacific      None    Tongatapu\n",
       "Pacific/Wake        Pacific      None         Wake\n",
       "Pacific/Wallis      Pacific      None       Wallis\n",
       "\n",
       "[417 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pytz\n",
    "\n",
    "def generate_timezones():\n",
    "    for timezone in pytz.common_timezones:\n",
    "        split = timezone.replace(\"_\", \" \").split(\"/\")\n",
    "        match len(split):\n",
    "            case 1: continue\n",
    "            case 2: region, subregion, place = split[0], None,     split[1]\n",
    "            case 3: region, subregion, place = split[0], split[1], split[2]\n",
    "            case _: raise ValueError(f\"Unexpected timezone format: {timezone}\")\n",
    "        if region == \"US\" or region == \"Canada\":\n",
    "            continue\n",
    "        yield timezone, region, subregion, place\n",
    "\n",
    "df_timezones = pd.DataFrame(\n",
    "    list(generate_timezones()),\n",
    "    columns=['timezone', 'region', 'subregion', 'place']\n",
    ").set_index('timezone')\n",
    "\n",
    "df_timezones"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get a city-coordinates database from `libtimezonemap`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>timezone</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>les Escaldes</th>\n",
       "      <td>42.50729</td>\n",
       "      <td>1.53414</td>\n",
       "      <td>Europe/Andorra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra la Vella</th>\n",
       "      <td>42.50779</td>\n",
       "      <td>1.52109</td>\n",
       "      <td>Europe/Andorra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Umm al Qaywayn</th>\n",
       "      <td>25.56473</td>\n",
       "      <td>55.55517</td>\n",
       "      <td>Asia/Dubai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ras al-Khaimah</th>\n",
       "      <td>25.78953</td>\n",
       "      <td>55.94320</td>\n",
       "      <td>Asia/Dubai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Khawr Fakkan</th>\n",
       "      <td>25.33132</td>\n",
       "      <td>56.34199</td>\n",
       "      <td>Asia/Dubai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bulawayo</th>\n",
       "      <td>-20.15000</td>\n",
       "      <td>28.58333</td>\n",
       "      <td>Africa/Harare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bindura</th>\n",
       "      <td>-17.30192</td>\n",
       "      <td>31.33056</td>\n",
       "      <td>Africa/Harare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beitbridge</th>\n",
       "      <td>-22.21667</td>\n",
       "      <td>30.00000</td>\n",
       "      <td>Africa/Harare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Epworth</th>\n",
       "      <td>-17.89000</td>\n",
       "      <td>31.14750</td>\n",
       "      <td>Africa/Harare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chitungwiza</th>\n",
       "      <td>-18.01274</td>\n",
       "      <td>31.07555</td>\n",
       "      <td>Africa/Harare</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23461 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  latitude  longitude        timezone\n",
       "city                                                 \n",
       "les Escaldes      42.50729    1.53414  Europe/Andorra\n",
       "Andorra la Vella  42.50779    1.52109  Europe/Andorra\n",
       "Umm al Qaywayn    25.56473   55.55517      Asia/Dubai\n",
       "Ras al-Khaimah    25.78953   55.94320      Asia/Dubai\n",
       "Khawr Fakkan      25.33132   56.34199      Asia/Dubai\n",
       "...                    ...        ...             ...\n",
       "Bulawayo         -20.15000   28.58333   Africa/Harare\n",
       "Bindura          -17.30192   31.33056   Africa/Harare\n",
       "Beitbridge       -22.21667   30.00000   Africa/Harare\n",
       "Epworth          -17.89000   31.14750   Africa/Harare\n",
       "Chitungwiza      -18.01274   31.07555   Africa/Harare\n",
       "\n",
       "[23461 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    '/usr/share/libtimezonemap/ui/cities15000.txt',\n",
    "    delimiter='\\t',\n",
    "    header=None,\n",
    "    usecols=[2, 4, 5, 17],\n",
    "    names=['city', 'latitude', 'longitude', 'timezone'],\n",
    "    quotechar='_'  # allow all other weird characters\n",
    ").set_index('city')\n",
    "\n",
    "df.index = df.index.map(lambda x: x.replace(\"'\", \" \"))\n",
    "\n",
    "df_cities = df\n",
    "\n",
    "df_cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find timezones coordinates in the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Solve some by finding unique timezones in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timezone</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>America/Antigua</th>\n",
       "      <td>17.11717</td>\n",
       "      <td>-61.84573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Anguilla</th>\n",
       "      <td>18.21704</td>\n",
       "      <td>-63.05783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Argentina/Ushuaia</th>\n",
       "      <td>-54.80000</td>\n",
       "      <td>-68.30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Pago_Pago</th>\n",
       "      <td>-14.27806</td>\n",
       "      <td>-170.70250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia/Broken_Hill</th>\n",
       "      <td>-31.96173</td>\n",
       "      <td>141.45998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/North_Dakota/New_Salem</th>\n",
       "      <td>46.82666</td>\n",
       "      <td>-100.88958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Europe/Vatican</th>\n",
       "      <td>41.90236</td>\n",
       "      <td>12.45332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Efate</th>\n",
       "      <td>-17.73381</td>\n",
       "      <td>168.32188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Wallis</th>\n",
       "      <td>-13.28163</td>\n",
       "      <td>-176.17453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Apia</th>\n",
       "      <td>-13.83333</td>\n",
       "      <td>-171.76666</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                latitude  longitude\n",
       "timezone                                           \n",
       "America/Antigua                 17.11717  -61.84573\n",
       "America/Anguilla                18.21704  -63.05783\n",
       "America/Argentina/Ushuaia      -54.80000  -68.30000\n",
       "Pacific/Pago_Pago              -14.27806 -170.70250\n",
       "Australia/Broken_Hill          -31.96173  141.45998\n",
       "...                                  ...        ...\n",
       "America/North_Dakota/New_Salem  46.82666 -100.88958\n",
       "Europe/Vatican                  41.90236   12.45332\n",
       "Pacific/Efate                  -17.73381  168.32188\n",
       "Pacific/Wallis                 -13.28163 -176.17453\n",
       "Pacific/Apia                   -13.83333 -171.76666\n",
       "\n",
       "[68 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_results = df_cities.drop_duplicates(subset=['timezone'], keep=False).set_index('timezone')\n",
    "\n",
    "df_timezones = df_timezones[~df_timezones.index.isin(df_results.index)]  # remove found timezones\n",
    "\n",
    "df_results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Africa/El_Aaiun not matched to any city\n",
      "Africa/Ndjamena not matched to any city\n",
      "((Hint) Africa/Tripoli: found city Tripoli but its timezone was Europe/Athens)\n",
      "((Hint) Africa/Tripoli: found city Tripoli but its timezone was Asia/Beirut)\n",
      "America/Adak not matched to any city\n",
      "America/Argentina/Catamarca not matched to any city\n",
      "America/Argentina/Jujuy not matched to any city\n",
      "America/Argentina/Tucuman not matched to any city\n",
      "America/Aruba not matched to any city\n",
      "America/Atikokan not matched to any city\n",
      "America/Bahia not matched to any city\n",
      "America/Bahia_Banderas not matched to any city\n",
      "((Hint) America/Belem: found city Belem but its timezone was America/Fortaleza)\n",
      "America/Belize not matched to any city\n",
      "America/Blanc-Sablon not matched to any city\n",
      "America/Cambridge_Bay not matched to any city\n",
      "((Hint) America/Cayenne: found city Cayenne but its timezone was Europe/Paris)\n",
      "((Hint) America/Ciudad_Juarez: found city Ciudad Juarez but its timezone was America/Ojinaga)\n",
      "America/Ciudad_Juarez not matched to any city\n",
      "America/Costa_Rica not matched to any city\n",
      "America/Creston not matched to any city\n",
      "America/Danmarkshavn not matched to any city\n",
      "America/Dawson not matched to any city\n",
      "America/El_Salvador not matched to any city\n",
      "America/Fort_Nelson not matched to any city\n",
      "America/Goose_Bay not matched to any city\n",
      "America/Guadeloupe not matched to any city\n",
      "America/Guatemala not matched to any city\n",
      "America/Guyana not matched to any city\n",
      "America/Indiana/Knox not matched to any city\n",
      "America/Indiana/Marengo not matched to any city\n",
      "((Hint) America/Indiana/Petersburg: found city Petersburg but its timezone was America/New_York)\n",
      "America/Indiana/Petersburg not matched to any city\n",
      "America/Indiana/Tell_City not matched to any city\n",
      "America/Indiana/Vevay not matched to any city\n",
      "((Hint) America/Indiana/Vincennes: found city Vincennes but its timezone was Europe/Paris)\n",
      "America/Indiana/Winamac not matched to any city\n",
      "America/Inuvik not matched to any city\n",
      "America/Iqaluit not matched to any city\n",
      "((Hint) America/Jamaica: found city Jamaica but its timezone was America/New_York)\n",
      "America/Jamaica not matched to any city\n",
      "America/Kentucky/Monticello not matched to any city\n",
      "((Hint) America/La_Paz: found city La Paz but its timezone was America/Argentina/Cordoba)\n",
      "((Hint) America/Los_Angeles: found city Los Angeles but its timezone was America/Santiago)\n",
      "America/Martinique not matched to any city\n",
      "((Hint) America/Matamoros: found city Matamoros but its timezone was America/Monterrey)\n",
      "America/Matamoros not matched to any city\n",
      "((Hint) America/Merida: found city Merida but its timezone was Europe/Madrid)\n",
      "America/Metlakatla not matched to any city\n",
      "America/Montserrat not matched to any city\n",
      "America/New_York not matched to any city\n",
      "America/Nome not matched to any city\n",
      "America/Noronha not matched to any city\n",
      "America/North_Dakota/Beulah not matched to any city\n",
      "America/North_Dakota/Center not matched to any city\n",
      "((Hint) America/Nuuk: found city Nuuk but its timezone was America/Godthab)\n",
      "America/Nuuk not matched to any city\n",
      "America/Port_of_Spain not matched to any city\n",
      "((Hint) America/Puerto_Rico: found city Puerto Rico but its timezone was America/Argentina/Cordoba)\n",
      "America/Puerto_Rico not matched to any city\n",
      "((Hint) America/Punta_Arenas: found city Punta Arenas but its timezone was America/Santiago)\n",
      "America/Punta_Arenas not matched to any city\n",
      "America/Rankin_Inlet not matched to any city\n",
      "America/Resolute not matched to any city\n",
      "((Hint) America/Santiago: found city Santiago but its timezone was America/Sao_Paulo)\n",
      "((Hint) America/Santo_Domingo: found city Santo Domingo but its timezone was America/Havana)\n",
      "America/Scoresbysund not matched to any city\n",
      "America/Sitka not matched to any city\n",
      "America/St_Johns not matched to any city\n",
      "America/St_Thomas not matched to any city\n",
      "America/St_Vincent not matched to any city\n",
      "America/Swift_Current not matched to any city\n",
      "America/Thule not matched to any city\n",
      "America/Yakutat not matched to any city\n",
      "Antarctica/Casey not matched to any city\n",
      "((Hint) Antarctica/Davis: found city Davis but its timezone was America/Los_Angeles)\n",
      "Antarctica/Davis not matched to any city\n",
      "Antarctica/DumontDUrville not matched to any city\n",
      "Antarctica/Macquarie not matched to any city\n",
      "Antarctica/Mawson not matched to any city\n",
      "Antarctica/McMurdo not matched to any city\n",
      "((Hint) Antarctica/Palmer: found city Palmer but its timezone was America/New_York)\n",
      "Antarctica/Palmer not matched to any city\n",
      "Antarctica/Rothera not matched to any city\n",
      "Antarctica/Syowa not matched to any city\n",
      "Antarctica/Troll not matched to any city\n",
      "Antarctica/Vostok not matched to any city\n",
      "Asia/Anadyr not matched to any city\n",
      "Asia/Aqtau not matched to any city\n",
      "((Hint) Asia/Atyrau: found city Atyrau but its timezone was Asia/Oral)\n",
      "Asia/Atyrau not matched to any city\n",
      "Asia/Bahrain not matched to any city\n",
      "((Hint) Asia/Barnaul: found city Barnaul but its timezone was Asia/Omsk)\n",
      "Asia/Barnaul not matched to any city\n",
      "((Hint) Asia/Chita: found city Chita but its timezone was Asia/Yakutsk)\n",
      "Asia/Chita not matched to any city\n",
      "((Hint) Asia/Colombo: found city Colombo but its timezone was America/Sao_Paulo)\n",
      "((Hint) Asia/Famagusta: found city Famagusta but its timezone was Asia/Nicosia)\n",
      "Asia/Famagusta not matched to any city\n",
      "Asia/Ho_Chi_Minh not matched to any city\n",
      "((Hint) Asia/Hovd: found city Hovd but its timezone was Asia/Ulaanbaatar)\n",
      "Asia/Hovd not matched to any city\n",
      "Asia/Kamchatka not matched to any city\n",
      "Asia/Khandyga not matched to any city\n",
      "Asia/Kuwait not matched to any city\n",
      "Asia/Qatar not matched to any city\n",
      "Asia/Qostanay not matched to any city\n",
      "Asia/Qyzylorda not matched to any city\n",
      "Asia/Sakhalin not matched to any city\n",
      "Asia/Samarkand not matched to any city\n",
      "Asia/Srednekolymsk not matched to any city\n",
      "((Hint) Asia/Tomsk: found city Tomsk but its timezone was Asia/Novosibirsk)\n",
      "Asia/Tomsk not matched to any city\n",
      "Asia/Urumqi not matched to any city\n",
      "Asia/Ust-Nera not matched to any city\n",
      "((Hint) Asia/Yangon: found city Yangon but its timezone was Asia/Rangoon)\n",
      "Asia/Yangon not matched to any city\n",
      "Atlantic/Canary not matched to any city\n",
      "Atlantic/Cape_Verde not matched to any city\n",
      "Atlantic/Madeira not matched to any city\n",
      "Australia/Eucla not matched to any city\n",
      "Australia/Lindeman not matched to any city\n",
      "Australia/Lord_Howe not matched to any city\n",
      "Europe/Andorra not matched to any city\n",
      "Europe/Astrakhan not matched to any city\n",
      "Europe/Busingen not matched to any city\n",
      "((Hint) Europe/Kirov: found city Kirov but its timezone was Europe/Volgograd)\n",
      "((Hint) Europe/Kirov: found city Kirov but its timezone was Europe/Moscow)\n",
      "Europe/Kirov not matched to any city\n",
      "Europe/Kyiv not matched to any city\n",
      "((Hint) Europe/London: found city London but its timezone was America/Toronto)\n",
      "((Hint) Europe/Madrid: found city Madrid but its timezone was America/Bogota)\n",
      "Europe/Malta not matched to any city\n",
      "((Hint) Europe/Saratov: found city Saratov but its timezone was Europe/Volgograd)\n",
      "Europe/Saratov not matched to any city\n",
      "Europe/Tirane not matched to any city\n",
      "((Hint) Europe/Ulyanovsk: found city Ulyanovsk but its timezone was Europe/Moscow)\n",
      "Europe/Ulyanovsk not matched to any city\n",
      "Indian/Chagos not matched to any city\n",
      "Indian/Comoro not matched to any city\n",
      "Indian/Mauritius not matched to any city\n",
      "Indian/Mayotte not matched to any city\n",
      "Indian/Reunion not matched to any city\n",
      "((Hint) Pacific/Chatham: found city Chatham but its timezone was Europe/London)\n",
      "Pacific/Chatham not matched to any city\n",
      "Pacific/Chuuk not matched to any city\n",
      "Pacific/Easter not matched to any city\n",
      "Pacific/Fakaofo not matched to any city\n",
      "Pacific/Fiji not matched to any city\n",
      "Pacific/Galapagos not matched to any city\n",
      "Pacific/Gambier not matched to any city\n",
      "Pacific/Guam not matched to any city\n",
      "Pacific/Kanton not matched to any city\n",
      "Pacific/Kiritimati not matched to any city\n",
      "Pacific/Kosrae not matched to any city\n",
      "Pacific/Kwajalein not matched to any city\n",
      "Pacific/Marquesas not matched to any city\n",
      "Pacific/Midway not matched to any city\n",
      "Pacific/Nauru not matched to any city\n",
      "Pacific/Tahiti not matched to any city\n",
      "Pacific/Wake not matched to any city\n"
     ]
    }
   ],
   "source": [
    "for timezone_row in df_timezones.itertuples():\n",
    "    timezone = timezone_row.Index\n",
    "    found = False\n",
    "    for city in df_cities.itertuples():\n",
    "        city_name = city.Index\n",
    "        if timezone_row.place == city_name:\n",
    "            if timezone == city.timezone:\n",
    "                found = True\n",
    "                df_timezones = df_timezones.drop(timezone)\n",
    "                df_results.loc[timezone] = [city.latitude, city.longitude]\n",
    "                break\n",
    "            print(f\"((Hint) {timezone}: found city {city_name} but its timezone was {city.timezone})\")\n",
    "\n",
    "    if not found:\n",
    "        print(f\"{timezone} not matched to any city\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remaining timezones to match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>subregion</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timezone</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Africa/El_Aaiun</th>\n",
       "      <td>Africa</td>\n",
       "      <td>None</td>\n",
       "      <td>El Aaiun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Africa/Ndjamena</th>\n",
       "      <td>Africa</td>\n",
       "      <td>None</td>\n",
       "      <td>Ndjamena</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Adak</th>\n",
       "      <td>America</td>\n",
       "      <td>None</td>\n",
       "      <td>Adak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Argentina/Catamarca</th>\n",
       "      <td>America</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Catamarca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Argentina/Jujuy</th>\n",
       "      <td>America</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>Jujuy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Marquesas</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Marquesas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Midway</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Midway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Nauru</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Nauru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Tahiti</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Tahiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Wake</th>\n",
       "      <td>Pacific</td>\n",
       "      <td>None</td>\n",
       "      <td>Wake</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>127 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              region  subregion      place\n",
       "timezone                                                  \n",
       "Africa/El_Aaiun               Africa       None   El Aaiun\n",
       "Africa/Ndjamena               Africa       None   Ndjamena\n",
       "America/Adak                 America       None       Adak\n",
       "America/Argentina/Catamarca  America  Argentina  Catamarca\n",
       "America/Argentina/Jujuy      America  Argentina      Jujuy\n",
       "...                              ...        ...        ...\n",
       "Pacific/Marquesas            Pacific       None  Marquesas\n",
       "Pacific/Midway               Pacific       None     Midway\n",
       "Pacific/Nauru                Pacific       None      Nauru\n",
       "Pacific/Tahiti               Pacific       None     Tahiti\n",
       "Pacific/Wake                 Pacific       None       Wake\n",
       "\n",
       "[127 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_timezones"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Matched timezones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timezone</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>America/Antigua</th>\n",
       "      <td>17.11717</td>\n",
       "      <td>-61.84573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Anguilla</th>\n",
       "      <td>18.21704</td>\n",
       "      <td>-63.05783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Argentina/Ushuaia</th>\n",
       "      <td>-54.80000</td>\n",
       "      <td>-68.30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Pago_Pago</th>\n",
       "      <td>-14.27806</td>\n",
       "      <td>-170.70250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia/Broken_Hill</th>\n",
       "      <td>-31.96173</td>\n",
       "      <td>141.45998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Auckland</th>\n",
       "      <td>-36.86667</td>\n",
       "      <td>174.76667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Honolulu</th>\n",
       "      <td>21.30694</td>\n",
       "      <td>-157.85833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Majuro</th>\n",
       "      <td>7.08971</td>\n",
       "      <td>171.38027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Noumea</th>\n",
       "      <td>-22.27631</td>\n",
       "      <td>166.45720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Port_Moresby</th>\n",
       "      <td>-9.44314</td>\n",
       "      <td>147.17972</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>293 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           latitude  longitude\n",
       "timezone                                      \n",
       "America/Antigua            17.11717  -61.84573\n",
       "America/Anguilla           18.21704  -63.05783\n",
       "America/Argentina/Ushuaia -54.80000  -68.30000\n",
       "Pacific/Pago_Pago         -14.27806 -170.70250\n",
       "Australia/Broken_Hill     -31.96173  141.45998\n",
       "...                             ...        ...\n",
       "Pacific/Auckland          -36.86667  174.76667\n",
       "Pacific/Honolulu           21.30694 -157.85833\n",
       "Pacific/Majuro              7.08971  171.38027\n",
       "Pacific/Noumea            -22.27631  166.45720\n",
       "Pacific/Port_Moresby       -9.44314  147.17972\n",
       "\n",
       "[293 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Search remaining timezones online"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Africa/El_Aaiun matched to Laâyoune العيون, Pachalik de Laâyoune باشوية العيون, Province de Laâyoune إقليم العيون, Maroc ⵍⵎⵖⵔⵉⴱ المغرب\n",
      "Africa/Ndjamena matched to N'Djaména انجمينا, Tchad تشاد\n",
      "America/Adak matched to Adak, Aleutians West Census Area, Alaska, 99546, United States\n",
      "America/Argentina/Catamarca matched to Catamarca, Argentina\n",
      "America/Argentina/Jujuy matched to Jujuy, Argentina\n",
      "America/Argentina/Tucuman matched to Tucumán, Argentina\n",
      "America/Aruba matched to Aruba, Nederland\n",
      "America/Atikokan matched to Atikokan, Rainy River District, Northwestern Ontario, Ontario, Canada\n",
      "America/Bahia matched to Bahia, Região Nordeste, Brasil\n",
      "America/Bahia_Banderas matched to Bahía Banderas, Bahías de Jaltenco, Alborada Jaltenco, Jaltenco, 55783, México\n",
      "America/Belize matched to Belize\n",
      "America/Blanc-Sablon matched to Blanc-Sablon, Le Golfe-du-Saint-Laurent, Côte-Nord, Québec, Canada\n",
      "America/Cambridge_Bay matched to Iqaluktuuttiaq (Cambridge Bay), ᕿᑎᕐᒥᐅᑦ Kitikmeot Region, ᓄᓇᕗᑦ Nunavut, X0B 0C0, Canada\n",
      "America/Ciudad_Juarez matched to Ciudad Juárez, Juárez, Chihuahua, 32001, México\n",
      "America/Costa_Rica matched to Costa Rica\n",
      "America/Creston matched to Creston, Union County, Iowa, 50801, United States\n",
      "America/Danmarkshavn matched to Danmarkshavn, Kalaallit Nunaat\n",
      "America/Dawson matched to Dawson County, Texas, United States\n",
      "America/El_Salvador matched to El Salvador\n",
      "America/Fort_Nelson matched to Fort Nelson, Northern Rockies Regional Municipality, British Columbia, V0C 1R0, Canada\n",
      "America/Goose_Bay matched to Goose Bay, Happy Valley-Goose Bay, Labrador, Newfoundland and Labrador, Canada\n",
      "America/Guadeloupe matched to Guadeloupe, France\n",
      "America/Guatemala matched to Guatemala\n",
      "America/Guyana matched to Guyana\n",
      "America/Indiana/Knox matched to Knox County, Indiana, United States\n",
      "America/Indiana/Marengo matched to Marengo, Crawford County, Indiana, United States\n",
      "America/Indiana/Petersburg matched to Petersburg, Pike County, Indiana, United States\n",
      "America/Indiana/Tell_City matched to Tell City, Perry County, Indiana, United States\n",
      "America/Indiana/Vevay matched to Vevay, Switzerland County, Indiana, United States\n",
      "America/Indiana/Winamac matched to Winamac, Pulaski County, Indiana, United States\n",
      "America/Inuvik matched to Inuvik, Gwich'in Settlement Area, Northwest Territories, Canada\n",
      "America/Iqaluit matched to Iqaluit ᐃᖃᓗᐃᑦ, ᐃᖃᓗᐃᑦ, ᕿᑭᖅᑖᓗᒃ Qikiqtaaluk Region, ᓄᓇᕗᑦ Nunavut, Canada\n",
      "America/Jamaica matched to Jamaica\n",
      "America/Kentucky/Monticello matched to Monticello, Wayne County, Kentucky, 42633, United States\n",
      "America/Martinique matched to Martinique, France\n",
      "America/Matamoros matched to Matamoros, Tamaulipas, 87300, México\n",
      "America/Metlakatla matched to Metlakatla, Unorganized Borough, Alaska, 99926, United States\n",
      "America/Montserrat matched to Montserrat\n",
      "America/New_York matched to City of New York, New York, United States\n",
      "America/Nome matched to Nome, Unorganized Borough, Alaska, United States\n",
      "America/Noronha matched to Olímpio Noronha, Região Geográfica Imediata de São Lourenço, Região Geográfica Intermediária de Pouso Alegre, Minas Gerais, Região Sudeste, Brasil\n",
      "America/North_Dakota/Beulah matched to Beulah, Mercer County, North Dakota, United States\n",
      "America/North_Dakota/Center matched to Center, Oliver County, North Dakota, United States\n",
      "America/Nuuk matched to Nuuk, Sermersooq, 3900, Kalaallit Nunaat\n",
      "America/Port_of_Spain matched to Port of Spain, Trinidad and Tobago\n",
      "America/Puerto_Rico matched to Puerto Rico, United States\n",
      "America/Punta_Arenas matched to Punta Arenas, Provincia de Magallanes, Región de Magallanes y de la Antártica Chilena, Chile\n",
      "America/Rankin_Inlet matched to ᑲᖏᕿᓂᖅ Rankin Inlet, ᑭᕙᓪᓕᖅ Kivalliq Region, ᓄᓇᕗᑦ Nunavut, Canada\n",
      "America/Resolute matched to Resolute, ᕿᑭᖅᑖᓗᒃ Qikiqtaaluk Region, ᓄᓇᕗᑦ Nunavut, X0A 0V0, Canada\n",
      "America/Scoresbysund matched to Ittoqqortoormiit, Sermersooq, 3980, Kalaallit Nunaat\n",
      "America/Sitka matched to Sitka, Alaska, 99835, United States\n",
      "America/St_Johns matched to St. John's, Saint John, Antigua, Antigua and Barbuda\n",
      "America/St_Thomas matched to Saint Thomas, Charlotte Amalie, Saint Thomas - Saint John District, United States Virgin Islands, United States\n",
      "America/St_Vincent matched to St. Vincent, Kittson County, Minnesota, 56755, United States\n",
      "America/Swift_Current matched to Swift Current, Division No. 8, Saskatchewan, Canada\n",
      "America/Thule matched to Thule, Qacha's Nek District, Lesotho\n",
      "America/Yakutat matched to Yakutat, Alaska, United States\n",
      "Antarctica/Casey matched to Casey County, Kentucky, United States\n",
      "Antarctica/Davis matched to Davis County, Iowa, United States\n",
      "No coordinates found for Antarctica/DumontDUrville\n",
      "Antarctica/Macquarie matched to Macquarie Island, Huon Valley, Tasmania, Australia\n",
      "Antarctica/Mawson matched to Mawson, District of Woden Valley, Australian Capital Territory, 2607, Australia\n",
      "Antarctica/McMurdo matched to McMurdo, Area A (Kicking Horse/Kinbasket Lake), Columbia-Shuswap Regional District, British Columbia, V0A 1H7, Canada\n",
      "Antarctica/Palmer matched to Palmer, Matanuska-Susitna, Alaska, 99645, United States\n",
      "Antarctica/Rothera matched to Rothera Research Station\n",
      "Antarctica/Syowa matched to 昭和基地\n",
      "Antarctica/Troll matched to Troll, Flyplassveien, Troll\n",
      "Antarctica/Vostok matched to Vostok\n",
      "Asia/Anadyr matched to Анадырь, городской округ Анадырь, Чукотский автономный округ, Дальневосточный федеральный округ, 689000, Россия\n",
      "Asia/Aqtau matched to Ақтау Қ.Ә., Маңғыстау облысы, Қазақстан\n",
      "Asia/Atyrau matched to Атырау облысы, Қазақстан\n",
      "Asia/Bahrain matched to البحرين\n",
      "Asia/Barnaul matched to Барнаул, городской округ Барнаул, Алтайский край, Сибирский федеральный округ, 656000, Россия\n",
      "Asia/Chita matched to Чита, городской округ Чита, Забайкальский край, Дальневосточный федеральный округ, 672000, Россия\n",
      "Asia/Famagusta matched to Gazimağusa, Gazimağusa Belediyesi, Gazimağusa ilçesi, Kuzey Kıbrıs, Κύπρος - Kıbrıs\n",
      "Asia/Ho_Chi_Minh matched to Ho Chi Minh, Muralla Street, Barangay 654, Intramuros, Fifth District, Manila, Capital District, Metro Manila, 1002, Pilipinas\n",
      "Asia/Hovd matched to Ховд ᠬᠣᠪᠳᠤ, Монгол улс ᠮᠤᠩᠭᠤᠯ ᠤᠯᠤᠰ\n",
      "Asia/Kamchatka matched to Камчатский край, Дальневосточный федеральный округ, Россия\n",
      "Asia/Khandyga matched to Хандыга, городское поселение Хандыга, Томпонский улус, Республика Саха (Якутия), Дальневосточный федеральный округ, 678720, Россия\n",
      "Asia/Kuwait matched to مدينة الكويت, إدارة النظافة العامة وإشغالات الطرق, العاصمة, 15005, الكويت\n",
      "Asia/Qatar matched to قطر\n",
      "Asia/Qostanay matched to Қостанай облысы, Қазақстан\n",
      "Asia/Qyzylorda matched to Қызылорда облысы, Қазақстан\n",
      "Asia/Sakhalin matched to Сахалин, Дальневосточный федеральный округ, Россия\n",
      "Asia/Samarkand matched to Samarqand shahri, Samarqand Viloyati, 140000, Oʻzbekiston\n",
      "Asia/Srednekolymsk matched to Среднеколымск, городское поселение Среднеколымск, Среднеколымский улус, Республика Саха (Якутия), Дальневосточный федеральный округ, Россия\n",
      "Asia/Tomsk matched to Томская область, Сибирский федеральный округ, Россия\n",
      "Asia/Urumqi matched to 乌鲁木齐县, 乌鲁木齐市 ئۈرۈمچى, 新疆维吾尔自治区 شىنجاڭ ئۇيغۇر ئاپتونوم رايونی, 830000, 中国\n",
      "Asia/Ust-Nera matched to Усть-Нера, городское поселение Усть-Нера, Оймяконский улус, Республика Саха (Якутия), Дальневосточный федеральный округ, 678730, Россия\n",
      "Asia/Yangon matched to Yangon, ရန်ကုန်တိုင်းဒေသကြီး, မြန်မာ\n",
      "Atlantic/Canary matched to Canary, Madison County, Texas, United States\n",
      "Atlantic/Cape_Verde matched to Cabo Verde\n",
      "Atlantic/Madeira matched to Madeira, Portugal\n",
      "Australia/Eucla matched to Eucla, Shire Of Dundas, Western Australia, Australia\n",
      "Australia/Lindeman matched to Lindeman, Uusikaupunki, Vakka-Suomen seutukunta, Varsinais-Suomi, Lounais-Suomen aluehallintovirasto, Manner-Suomi, Suomi / Finland\n",
      "Australia/Lord_Howe matched to Lord Howe Island, New South Wales, 2898, Australia\n",
      "Europe/Andorra matched to Andorra\n",
      "Europe/Astrakhan matched to Астрахань, городской округ Астрахань, Астраханская область, Южный федеральный округ, 414000, Россия\n",
      "Europe/Busingen matched to Büsingen am Hochrhein, Verwaltungsgemeinschaft Gottmadingen, Landkreis Konstanz, Baden-Württemberg, 78266, Deutschland\n",
      "Europe/Kirov matched to Киров, городской округ Киров, Кировская область, Приволжский федеральный округ, 610000, Россия\n",
      "Europe/Kyiv matched to Київ, Україна\n",
      "Europe/Malta matched to Malta\n",
      "Europe/Saratov matched to Саратов, городской округ Саратов, Саратовская область, Приволжский федеральный округ, 410000, Россия\n",
      "Europe/Tirane matched to Tiranë, Bashkia Tiranë, Qarku i Tiranës, Shqipëria Qendrore, 1001-1028, Shqipëria\n",
      "Europe/Ulyanovsk matched to Ульяновск, городской округ Ульяновск, Ульяновская область, Приволжский федеральный округ, Россия\n",
      "Indian/Chagos matched to Chagos, La Vilieḷḷa, Ḷḷarón, Cangas del Narcea, 33811, España\n",
      "Indian/Comoro matched to Comoro, Asmat, Papua Selatan, Western New Guinea, Indonesia\n",
      "Indian/Mauritius matched to Mauritius / Maurice\n",
      "Indian/Mayotte matched to Mayotte, France\n",
      "Indian/Reunion matched to La Réunion, France\n",
      "Pacific/Chatham matched to Chatham County, North Carolina, United States\n",
      "Pacific/Chuuk matched to Chuuk, Micronesia\n",
      "Pacific/Easter matched to Easter, Castro County, Texas, United States\n",
      "Pacific/Fakaofo matched to Fakaofo, Tokelau\n",
      "Pacific/Fiji matched to Viti\n",
      "Pacific/Galapagos matched to Galápagos, Ecuador\n",
      "Pacific/Gambier matched to Gambier, College Township, Knox County, Ohio, United States\n",
      "Pacific/Guam matched to Guam, United States\n",
      "Pacific/Kanton matched to Kanton, Phoenix Islands, Kiribati\n",
      "Pacific/Kiritimati matched to Kiritimati, Line Islands, Kiribati\n",
      "Pacific/Kosrae matched to Kosrae, Utwe, Kosrae, 96944, Micronesia\n",
      "Pacific/Kwajalein matched to Kuwajleen, Rālik, Ṃajeḷ\n",
      "Pacific/Marquesas matched to Îles Marquises, Polynésie Française, France\n",
      "Pacific/Midway matched to Midway, Madison County, Texas, United States\n",
      "Pacific/Nauru matched to Naoero\n",
      "Pacific/Tahiti matched to Tahiti, Îles du Vent, Polynésie Française, France\n",
      "Pacific/Wake matched to Wake County, North Carolina, United States\n"
     ]
    }
   ],
   "source": [
    "from geopy.geocoders import Nominatim\n",
    "\n",
    "geolocator = Nominatim(user_agent=\"Cinnamon Desktop Applet Auto-night-light, manual single shot\")\n",
    "\n",
    "for timezone_row in df_timezones.itertuples():\n",
    "    timezone = timezone_row.Index\n",
    "    subregion = timezone_row.subregion\n",
    "    geocode = geolocator.geocode(f\"{timezone_row.place}{f\", {subregion}\" if subregion else \"\"}\")\n",
    "    if not geocode:\n",
    "        print(f\"No coordinates found for {timezone}\")\n",
    "        continue\n",
    "    print(f\"{timezone} matched to {geocode.address}\")\n",
    "    df_timezones = df_timezones.drop(timezone)\n",
    "    df_results.loc[timezone] = [round(geocode.latitude, 5), round(geocode.longitude, 5)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remaining timezones to match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>subregion</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timezone</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Antarctica/DumontDUrville</th>\n",
       "      <td>Antarctica</td>\n",
       "      <td>None</td>\n",
       "      <td>DumontDUrville</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               region subregion           place\n",
       "timezone                                                       \n",
       "Antarctica/DumontDUrville  Antarctica      None  DumontDUrville"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_timezones"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Matched timezones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timezone</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>America/Antigua</th>\n",
       "      <td>17.11717</td>\n",
       "      <td>-61.84573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Anguilla</th>\n",
       "      <td>18.21704</td>\n",
       "      <td>-63.05783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Argentina/Ushuaia</th>\n",
       "      <td>-54.80000</td>\n",
       "      <td>-68.30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Pago_Pago</th>\n",
       "      <td>-14.27806</td>\n",
       "      <td>-170.70250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia/Broken_Hill</th>\n",
       "      <td>-31.96173</td>\n",
       "      <td>141.45998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Marquesas</th>\n",
       "      <td>-9.77941</td>\n",
       "      <td>-139.00678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Midway</th>\n",
       "      <td>31.02602</td>\n",
       "      <td>-95.75050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Nauru</th>\n",
       "      <td>-0.52523</td>\n",
       "      <td>166.93244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Tahiti</th>\n",
       "      <td>-17.68734</td>\n",
       "      <td>-149.44517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Wake</th>\n",
       "      <td>35.79794</td>\n",
       "      <td>-78.61183</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>419 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           latitude  longitude\n",
       "timezone                                      \n",
       "America/Antigua            17.11717  -61.84573\n",
       "America/Anguilla           18.21704  -63.05783\n",
       "America/Argentina/Ushuaia -54.80000  -68.30000\n",
       "Pacific/Pago_Pago         -14.27806 -170.70250\n",
       "Australia/Broken_Hill     -31.96173  141.45998\n",
       "...                             ...        ...\n",
       "Pacific/Marquesas          -9.77941 -139.00678\n",
       "Pacific/Midway             31.02602  -95.75050\n",
       "Pacific/Nauru              -0.52523  166.93244\n",
       "Pacific/Tahiti            -17.68734 -149.44517\n",
       "Pacific/Wake               35.79794  -78.61183\n",
       "\n",
       "[419 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check and rectify the results manually\n",
    "\n",
    "Check procedure:\n",
    "- check if the `timezone` name makes sense with the address found,\n",
    "- if not:\n",
    "  - search on a web search engine if the full `timezone` gives similar results to the address found by Nomatim,\n",
    "  - if not: find better or more values for a new search"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "America/Creston matched to Creston, Regional District of Central Kootenay, British Columbia, Canada\n",
      "America/Dawson matched to Dawson City, Yukon, Y0B 1G0, Canada\n",
      "America/St_Johns matched to Saint Johns Island, Labrador, Newfoundland and Labrador, Canada\n",
      "America/St_Vincent matched to Saint Vincent and the Grenadines\n",
      "America/Thule matched to Qaanaaq, Avannaata, 3971, Kalaallit Nunaat\n",
      "Antarctica/Casey matched to Casey Station, Shirley Island Walking Route, Casey Station\n",
      "Antarctica/Davis matched to Davis Station\n",
      "Antarctica/DumontDUrville matched to Base Dumont d'Urville\n",
      "Antarctica/Mawson matched to Mawson Station, Alternate Route, Mawson Station\n",
      "Antarctica/McMurdo matched to McMurdo Station\n",
      "Asia/Ho_Chi_Minh matched to Thành phố Hồ Chí Minh, Việt Nam\n",
      "Atlantic/Canary matched to Canary Islands, España\n",
      "Australia/Lindeman matched to Lindeman Island, Coral Sea, Mackay Regional, Queensland, Australia\n",
      "Indian/Chagos matched to Chagos Archipelago, Downtown, British Indian Ocean Territory\n",
      "Indian/Comoro matched to Archipel des Comores, Nzwani أنجوان, Comores Komori جزر القمر\n",
      "Pacific/Chatham matched to Chatham Island, Chatham Islands, New Zealand / Aotearoa\n",
      "Pacific/Easter matched to Isla de Pascua, Provincia de Isla de Pascua, Región de Valparaíso, 2770000, Chile\n",
      "Pacific/Galapagos matched to Archipélago de Galápagos, Galápagos, Ecuador\n",
      "Pacific/Gambier matched to Îles Gambier, Gambier, Tuamotu-Gambier, Polynésie Française, 98755, France\n",
      "Pacific/Midway matched to Midway Atoll, United States\n",
      "Pacific/Wake matched to United States Minor Outlying Islands, United States\n"
     ]
    }
   ],
   "source": [
    "timezones_to_search_again = {\n",
    "    'America/Creston': {'city': \"Creston\", 'country': \"Canada\"},\n",
    "    'America/Dawson': {'city': \"Dawson\", 'country': \"Canada\"},\n",
    "    'America/St_Johns': \"Saint Johns Island, Canada\",\n",
    "    'America/St_Vincent': \"Saint Vincent and the Grenadines\",\n",
    "    'America/Thule': {'city': \"Thule\", 'country': \"Greenland\"},\n",
    "    'Antarctica/Casey': \"Casey Station\",\n",
    "    'Antarctica/Davis': \"Davis Station\",\n",
    "    'Antarctica/DumontDUrville': \"Dumont d'Urville Station\",\n",
    "    'Antarctica/Mawson': \"Mawson Station\",\n",
    "    'Antarctica/McMurdo': \"McMurdo Station\",\n",
    "    'Asia/Ho_Chi_Minh': {'city': \"Ho Chi Minh\", 'country': \"Vietnam\"},\n",
    "    'Atlantic/Canary': \"Canary Islands, Spain\",\n",
    "    'Australia/Lindeman': \"Lindeman Island, Australia\",\n",
    "    'Indian/Chagos': \"Chagos Archipelago\",\n",
    "    'Indian/Comoro': \"Comoro Islands\",\n",
    "    'Pacific/Chatham': \"Chatham, New Zealand\",\n",
    "    'Pacific/Easter': \"Easter Island, Chile\",\n",
    "    'Pacific/Galapagos': \"Galapagos Islands\",\n",
    "    'Pacific/Gambier': \"Gambier Islands\",\n",
    "    'Pacific/Midway': \"Midway Atoll\",\n",
    "    'Pacific/Wake': \"Minor Outlying Islands\"\n",
    "}\n",
    "\n",
    "for timezone, search in timezones_to_search_again.items():\n",
    "    geocode = geolocator.geocode(search)\n",
    "    if not geocode:\n",
    "        print(f\"No coordinates found for {timezone}\")\n",
    "        continue\n",
    "    print(f\"{timezone} matched to {geocode.address}\")\n",
    "    df_results.loc[timezone] = [round(geocode.latitude, 5), round(geocode.longitude, 5)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timezone</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>America/Antigua</th>\n",
       "      <td>17.11717</td>\n",
       "      <td>-61.84573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Anguilla</th>\n",
       "      <td>18.21704</td>\n",
       "      <td>-63.05783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>America/Argentina/Ushuaia</th>\n",
       "      <td>-54.80000</td>\n",
       "      <td>-68.30000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Pago_Pago</th>\n",
       "      <td>-14.27806</td>\n",
       "      <td>-170.70250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia/Broken_Hill</th>\n",
       "      <td>-31.96173</td>\n",
       "      <td>141.45998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Midway</th>\n",
       "      <td>28.24175</td>\n",
       "      <td>-177.37543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Nauru</th>\n",
       "      <td>-0.52523</td>\n",
       "      <td>166.93244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Tahiti</th>\n",
       "      <td>-17.68734</td>\n",
       "      <td>-149.44517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pacific/Wake</th>\n",
       "      <td>16.72882</td>\n",
       "      <td>-169.53338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antarctica/DumontDUrville</th>\n",
       "      <td>-66.66328</td>\n",
       "      <td>140.00110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>420 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           latitude  longitude\n",
       "timezone                                      \n",
       "America/Antigua            17.11717  -61.84573\n",
       "America/Anguilla           18.21704  -63.05783\n",
       "America/Argentina/Ushuaia -54.80000  -68.30000\n",
       "Pacific/Pago_Pago         -14.27806 -170.70250\n",
       "Australia/Broken_Hill     -31.96173  141.45998\n",
       "...                             ...        ...\n",
       "Pacific/Midway             28.24175 -177.37543\n",
       "Pacific/Nauru              -0.52523  166.93244\n",
       "Pacific/Tahiti            -17.68734 -149.44517\n",
       "Pacific/Wake               16.72882 -169.53338\n",
       "Antarctica/DumontDUrville -66.66328  140.00110\n",
       "\n",
       "[420 rows x 2 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "dict_results = df_results[['latitude', 'longitude']].T.to_dict('list')\n",
    "\n",
    "json_results = json.dumps(dict_results, sort_keys=True)\n",
    "\n",
    "with open('database.json', 'w') as f:\n",
    "    f.write(json_results)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
